package cn.yunyichina.provider.pay.service.impl;

import cn.yunyichina.provider.iface.entity.common.Response;
import cn.yunyichina.provider.iface.entity.common.ResponseObject;
import cn.yunyichina.provider.pay.config.Config;
import cn.yunyichina.provider.pay.entity.HisOrder;
import cn.yunyichina.provider.pay.entity.HisOrderCheckedError;
import cn.yunyichina.provider.pay.entity.PaymentCheckSourceFile;
import cn.yunyichina.provider.pay.entity.PaymentJournal;
import cn.yunyichina.provider.pay.service.FinanceExcelService;
import cn.yunyichina.provider.pay.service.FinanceService;
import cn.yunyichina.provider.pay.utils.NumberUtil;
import cn.yunyichina.provider.pay.vo.BillDetail;
import cn.yunyichina.provider.pay.vo.FinanceExcelVo;
import cn.yunyichina.provider.pay.vo.FinanceRecordsVo;
import cn.yunyichina.provider.pay.vo.Patstatistics;
import cn.yunyichina.utils.commons.DateUtils;
import cn.yunyichina.utils.commons.StringUtils;
import cn.yunyichina.utils.convert.JsonUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Joe.zhou on 2017/5/4.
 */
@Service
public class FinanceExcelImpl implements FinanceExcelService {

    private static Logger logger = LoggerFactory.getLogger(FinanceExcelImpl.class);

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

    @Resource
    private FinanceService financeService;

    @Resource
    private Properties yaml;

    /**
     * 平台订单流水统计Excel模板
     *
     * @param datas
     * @return
     */
    @Override
    public Response detailsPatstatisticsExcelTemplate(String datas, FinanceExcelVo request) {
        Response response = new Response();
        ResponseObject resObj = financeService.queryFinanceDetailsPatstatistics(datas);
        if ("0".equals(response.getResultCode())) {
            NumberFormat nf = new DecimalFormat("#,###.####");
            List<Patstatistics> pats = (List<Patstatistics>) resObj.getResultObject();
            String[] headers =
                    {"日期", "服务商名称", "总金额", "记账总金额", "实收总金额", "总笔数", "挂号金额", "挂号记账金额", "挂号实收金额", "挂号笔数", "门诊缴费金额", "门诊缴费记账金额", "门诊缴费实收金额", "门诊缴费笔数",
                            "住院缴费金额", "住院缴费记账金额", "住院缴费实收金额", "住院缴费笔数"};
            HSSFWorkbook wb = new HSSFWorkbook();
            OutputStream out = null;
            int sheetFirstRow = 0;
            int sheetlastRow = 0;
            String datesName = request.getBeginPayDate() + "—" + request.getEndPayDate();

            if (pats.size() > 0) {
                try {

                    HSSFFont titleFont = wb.createFont();
                    titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                    titleFont.setFontName("宋体");
                    titleFont.setFontHeightInPoints((short) 18);
                    HSSFCellStyle titleStyle = wb.createCellStyle();
                    titleStyle.setFont(titleFont);
                    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

                    // 生成一个样式
                    HSSFCellStyle style = wb.createCellStyle();
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中


                    Collections.sort(pats, new Comparator<Patstatistics>() {
                        @Override
                        public int compare(Patstatistics o1, Patstatistics o2) {
                            try {
                                Date dete1 = sdf.parse(o1.getPay_date() + " 00:00:00");
                                Date dete2 = sdf.parse(o2.getPay_date() + " 00:00:00");

                                if (dete1.getTime() > dete2.getTime()) {
                                    return 1;
                                }

                            } catch (ParseException e) {
                                e.printStackTrace();
                            }

                            return -1;
                        }
                    });

                    HSSFSheet sheet = wb.createSheet(datesName + " 平台订单流水统计");
                    // 产生表格标题行
                    int rowCount = 0;
                    HSSFRow ro = sheet.createRow(rowCount);
                    HSSFCell cel = ro.createCell(8);
                    cel.setCellValue(request.getOrgName() + "平台订单流水统计");
                    cel.setCellStyle(titleStyle);

                    sheet.setDefaultColumnWidth((short) 18);
                    HSSFRow row = sheet.createRow(rowCount + 1);
                    rowCount++;
                    for (short i = 0; i < headers.length; i++) {
                        HSSFCell cell = row.createCell(i);
                        cell.setCellStyle(style);
                        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }

                    for (Patstatistics pat : pats) {

                        List<BillDetail> details = pat.getBillDetails();

                        double totalAmount = 0;
                        double insAmount = 0;
                        double realAmount = 0;
                        int totalCount = 0;
                        double regAmount = 0;
                        double regInsAmount = 0;
                        double regRealAmount = 0;
                        int regCount = 0;
                        double mzAmount = 0;
                        double mzInsAmount = 0;
                        double mzRealAmount = 0;
                        int mzCount = 0;
                        double zyAmount = 0;
                        double zyInsAmount = 0;
                        double zyRealAmount = 0;
                        int zyCount = 0;

                        for (int i = 0; i < details.size(); i++) {
                            HSSFRow row2 = sheet.createRow(rowCount + 1);
                            HSSFCell cell = row2.createCell(i);
                            cell.setCellStyle(style);
                            cell.setCellValue(pat.getPay_date());

                            row2.createCell(1).setCellValue(details.get(i).getPlatformName());
                            row2.createCell(2).setCellValue(NumberUtil.changeF2Y(details.get(i).getTotalAmount()));
                            row2.createCell(3).setCellValue(NumberUtil.changeF2Y(details.get(i).getInsuranceAmount()));
                            row2.createCell(4).setCellValue(NumberUtil.changeF2Y(details.get(i).getRealAmount()));
                            row2.createCell(5).setCellValue(details.get(i).getTotalCount());
                            row2.createCell(6).setCellValue(NumberUtil.changeF2Y(details.get(i).getRegisterAmount()));
                            row2.createCell(7).setCellValue(NumberUtil.changeF2Y(details.get(i).getRegisterInsuranceAmount()));
                            row2.createCell(8).setCellValue(NumberUtil.changeF2Y(details.get(i).getRegisterRealAmount()));
                            row2.createCell(9).setCellValue(details.get(i).getRegisterCount());
                            row2.createCell(10).setCellValue(NumberUtil.changeF2Y(details.get(i).getMzFeeAmount()));
                            row2.createCell(11).setCellValue(NumberUtil.changeF2Y(details.get(i).getMzFeeInsuranceAmount()));
                            row2.createCell(12).setCellValue(NumberUtil.changeF2Y(details.get(i).getMzFeeRealAmount()));
                            row2.createCell(13).setCellValue(details.get(i).getMzFeeCount());
                            row2.createCell(14).setCellValue(NumberUtil.changeF2Y(details.get(i).getZyAmount()));
                            row2.createCell(15).setCellValue(NumberUtil.changeF2Y(details.get(i).getZyInsuranceAmount()));
                            row2.createCell(16).setCellValue(NumberUtil.changeF2Y(details.get(i).getZyRealAmount()));
                            row2.createCell(17).setCellValue(details.get(i).getZyCount());
                            rowCount++;

                            totalAmount = totalAmount + Double.valueOf(details.get(i).getTotalAmount());
                            insAmount = insAmount + Double.valueOf(details.get(i).getInsuranceAmount());
                            realAmount = realAmount + Double.valueOf(details.get(i).getRealAmount());
                            totalCount = totalCount + Integer.valueOf(details.get(i).getTotalCount());
                            regAmount = regAmount + Double.valueOf(details.get(i).getRegisterAmount());
                            regInsAmount = regInsAmount + Double.valueOf(details.get(i).getRegisterInsuranceAmount());
                            regRealAmount = regRealAmount + Double.valueOf(details.get(i).getRegisterRealAmount());
                            regCount = regCount + Integer.valueOf(details.get(i).getRegisterCount());
                            mzAmount = mzAmount + Double.valueOf(details.get(i).getMzFeeAmount());
                            mzInsAmount = mzInsAmount + Double.valueOf(details.get(i).getMzFeeInsuranceAmount());
                            mzRealAmount = mzRealAmount + Double.valueOf(details.get(i).getMzFeeRealAmount());
                            mzCount = mzCount + Integer.valueOf(details.get(i).getMzFeeCount());
                            zyAmount = zyAmount + Double.valueOf(details.get(i).getZyAmount());
                            zyInsAmount = zyInsAmount + Double.valueOf(details.get(i).getZyInsuranceAmount());
                            zyRealAmount = zyRealAmount + Double.valueOf(details.get(i).getZyRealAmount());
                            zyCount = zyCount + Integer.valueOf(details.get(i).getZyCount());
                        }

                        //合计
                        HSSFRow row3 = sheet.createRow(rowCount + 1);
                        row3.createCell(1).setCellValue("合计");
                        row3.createCell(2).setCellValue(nf.format(totalAmount/100));
                        row3.createCell(3).setCellValue(nf.format(insAmount/100));
                        row3.createCell(4).setCellValue(nf.format(realAmount/100));
                        row3.createCell(5).setCellValue(String.valueOf(totalCount));
                        row3.createCell(6).setCellValue(nf.format(regAmount/100));
                        row3.createCell(7).setCellValue(nf.format(regInsAmount/100));
                        row3.createCell(8).setCellValue(nf.format(regRealAmount/100));
                        row3.createCell(9).setCellValue(String.valueOf(regCount));
                        row3.createCell(10).setCellValue(nf.format(mzAmount/100));
                        row3.createCell(11).setCellValue(nf.format(mzInsAmount/100));
                        row3.createCell(12).setCellValue(nf.format(mzRealAmount/100));
                        row3.createCell(13).setCellValue(String.valueOf(mzCount));
                        row3.createCell(14).setCellValue(nf.format(zyAmount/100));
                        row3.createCell(15).setCellValue(nf.format(zyInsAmount/100));
                        row3.createCell(16).setCellValue(nf.format(zyRealAmount/100));
                        row3.createCell(17).setCellValue(String.valueOf(zyCount));
                        rowCount++;

                        if(sheetFirstRow == 0){
                            sheetFirstRow = 2;
                        }else{
                            sheetFirstRow = sheetFirstRow + details.size() + 1;
                        }

                        sheetlastRow = sheetFirstRow + details.size();
                        sheet.addMergedRegion(new CellRangeAddress(sheetFirstRow, sheetlastRow, 0,  0)); //第3行 第一列 合并5列
                    }

                    String savePath = yaml.getProperty(Config.CONFIG_PAY_ALIPAY_CHECK_ACCOUNT_PATH);
                    String fileName = request.getOrgName().trim() + "_平台订单流水统计.csv";
                    String filePath = URLDecoder.decode(savePath + fileName, "UTF-8");
                    out = new FileOutputStream(filePath);
                    wb.write(out);

                    logger.info("save success! " + filePath);
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("fileName", fileName);
                    map.put("filePath", filePath);

                    response.setResultCodeAndMessageAndResult(Response.SUCCESS_CODE, Response.SUCCESS_MESSAGE, JsonUtils.toJsonString(map));
                } catch (IOException e) {
                    logger.error("[FinanceExcelImpl.detailsPatstatisticsExcel] -- excelError", e);
                } finally {
                    if (out != null) {
                        try {
                            out.close();
                            wb.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } else {
                logger.info(request.getOrgId() + "_账单不存在.");
            }
        }

        return response;
    }


    /**
     * 医院订单流水明细Excel模板
     *
     * @param datas
     * @return
     */
    @Override
    public Response detailsHisdetailsExcelTemplate(String datas, FinanceExcelVo request) {
        Response response = new Response();
        Map<String, Object> objMap = financeService.queryFinanceDetailsHisdetails(datas);
        if (!objMap.isEmpty()) {
            NumberFormat nf = new DecimalFormat("#,###.####");
            List<HisOrder> records = (List<HisOrder>) objMap.get("records");
            String[] headers =
                    {"交易时间","平台订单号","HIS订单号","金额","业务类型","支付状态","对账类型","支付类型","操作员","患者姓名","支付时间","创建时间"};
            XSSFWorkbook wb = new XSSFWorkbook();
            OutputStream out = null;
            int sheetFirstRow = 0;
            int sheetlastRow = 0;
            String datesName = request.getBeginPayDate() + "—" + request.getEndPayDate();

            if (records.size() > 0) {
                try {

                    XSSFFont titleFont = wb.createFont();
                    titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                    titleFont.setFontName("宋体");
                    titleFont.setFontHeightInPoints((short) 20);
                    XSSFCellStyle titleStyle = wb.createCellStyle();
                    titleStyle.setFont(titleFont);
                    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

                    // 生成一个样式
                    XSSFCellStyle style = titleStyle;
                    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//左右居中
                    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//上下居中


                    Collections.sort(records, new Comparator<HisOrder>() {
                        @Override
                        public int compare(HisOrder o1, HisOrder o2) {
                            try {
                                Date dete1 = sdf.parse(o1.getCreatedTime() + " 00:00:00");
                                Date dete2 = sdf.parse(o2.getCreatedTime() + " 00:00:00");

                                if (dete1.getTime() > dete2.getTime()) {
                                    return 1;
                                }

                            } catch (ParseException e) {
                                e.printStackTrace();
                            }

                            return -1;
                        }
                    });

                    XSSFSheet sheet = wb.createSheet(datesName + " 医院订单流水明细");
                    // 产生表格标题行
                    int rowCount = 0;
                    XSSFRow ro = sheet.createRow(rowCount);
                    XSSFCell cel = ro.createCell(8);
                    cel.setCellValue(request.getOrgName() + "医院订单流水明细");
                    cel.setCellStyle(titleStyle);

                    sheet.setDefaultColumnWidth((short) 18);
                    XSSFRow row = sheet.createRow(rowCount + 1);
                    rowCount++;


                    Font ztFont = wb.createFont();
                    XSSFCellStyle ztStyle =  wb.createCellStyle();
                    ztFont.setFontHeightInPoints((short)12);
                    ztFont.setFontName("宋体");
                    ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    ztStyle.setFont(ztFont);
                    for (short i = 0; i < headers.length; i++) {
                        XSSFCell cell = row.createCell(i);
                        cell.setCellStyle(ztStyle);
                        XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }


                    int totalAmount = 0;
                    for (int i = 0; i < records.size(); i++) {
                        int column = 0;
                        totalAmount += records.get(i).getPayAmount();
                        XSSFRow row2 = sheet.createRow(rowCount + 1);
                        XSSFCell cell = row2.createCell(i);
                        cell.setCellStyle(style);
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getPayTime()) ? records.get(i).getPayTime() : DateUtils.getDateStr(DateUtils.StringToDateYMD(records.get(i).getPayTime())));
                        row2.createCell(column++).setCellValue(records.get(i).getYytOrderNo());
                        row2.createCell(column++).setCellValue(records.get(i).getHisOrderNo());
                        row2.createCell(column++).setCellValue(NumberUtil.changeF2Y(String.valueOf(records.get(i).getPayAmount())));
                        row2.createCell(column++).setCellValue(converOrderType(records.get(i).getOrderType()));
                        row2.createCell(column++).setCellValue(converPayStatus(records.get(i).getPayStatus()));
                        row2.createCell(column++).setCellValue(converCheckStatus(records.get(i).getCheckStatus()));
                        row2.createCell(column++).setCellValue(converPayMode(records.get(i).getPayMode()));
                        row2.createCell(column++).setCellValue(records.get(i).getCollectors());
                        row2.createCell(column++).setCellValue(records.get(i).getPatName());
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getPayTime()) ? records.get(i).getPayTime() : DateUtils.dateToString(DateUtils.StringToDate(records.get(i).getPayTime())));
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getCreatedTime()) ? records.get(i).getPayTime() :DateUtils.dateToString(DateUtils.StringToDate(records.get(i).getCreatedTime())));
                        rowCount++;

                    }

                    XSSFRow row3 = sheet.createRow(rowCount + 1);
                    XSSFCell cell = row3.createCell(0);
                    cell.setCellStyle(style);
                    row3.createCell(0).setCellValue("合计金额：");
                    row3.createCell(3).setCellValue(NumberUtil.changeF2Y(String.valueOf(totalAmount)));

                    String savePath = yaml.getProperty(Config.CONFIG_PAY_ALIPAY_CHECK_ACCOUNT_PATH);
                    String fileName = request.getOrgName().trim() + "_医院订单流水明细.csv";
                    String filePath = URLDecoder.decode(savePath + fileName, "UTF-8");
                    out = new FileOutputStream(filePath);
                    wb.write(out);

                    logger.info("save success! " + filePath);
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("fileName", fileName);
                    map.put("filePath", filePath);

                    response.setResultCodeAndMessageAndResult(Response.SUCCESS_CODE, Response.SUCCESS_MESSAGE, JsonUtils.toJsonString(map));
                } catch (IOException e) {
                    logger.error("[FinanceExcelImpl.detailsPatstatisticsExcel] -- excelError", e);
                } finally {
                    if (out != null) {
                        try {
                            out.close();
                            wb.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } else {
                logger.info(request.getOrgId() + "_账单不存在.");
            }
        }

        return response;
    }


    /**
     * 平台订单流水明细Excel模板
     *
     * @param datas
     * @return
     */
    @Override
    public Response detailsPatdetailsExcelTemplate(String datas, FinanceExcelVo request) {
        Response response = new Response();
        Map<String, Object> objMap = financeService.queryFinanceDetailsPatdetails(datas);
        if (!objMap.isEmpty()) {
            NumberFormat nf = new DecimalFormat("#,###.####");
            List<Map> recordMaps = (List<Map>) objMap.get("records");
            String[] headers =
                    {"交易时间","平台订单号","HIS订单号","收单机构流水号","金额","业务类型","支付状态","对账类型","支付类型","操作员","是否自动退款","患者姓名","支付时间","创建时间"};
            XSSFWorkbook wb = new XSSFWorkbook();
            OutputStream out = null;
            String datesName = request.getBeginPayDate() + "—" + request.getEndPayDate();

            if (recordMaps.size() > 0) {
                try {

                    ArrayList<PaymentJournal> journals = new ArrayList<>();

                    for (Map map : recordMaps) {
                        PaymentJournal paymentJournal = new PaymentJournal();
                        paymentJournal.setPayTime(map.get("pay_time") != null ? String.valueOf(map.get("pay_time")) : null);
                        paymentJournal.setOrderNo((String)map.get("order_no"));
                        paymentJournal.setHisOrderNo((String)map.get("his_order_no"));
                        paymentJournal.setPaymentDealId((String)map.get("payment_deal_id"));
                        paymentJournal.setPayAmount((int)map.get("pay_amount"));
                        paymentJournal.setOrderType(map.get("order_type") != null ? Short.valueOf(String.valueOf((int)map.get("order_type"))) : null);
                        paymentJournal.setPayMode(map.get("pay_mode") != null ? Short.valueOf(String.valueOf((int)map.get("pay_mode"))) : null);
                        paymentJournal.setPayStatus(map.get("pay_status") != null ? Short.valueOf(String.valueOf((int)map.get("pay_status"))) : null);
                        paymentJournal.setCheckStatus(map.get("check_status") != null ? Short.valueOf(String.valueOf((int)map.get("check_status"))) : null);
                        paymentJournal.setIsAutoRefund(map.get("is_auto_refund") != null ? Short.valueOf(String.valueOf((int)map.get("is_auto_refund"))) : 0);
                        paymentJournal.setBranchName((String)map.get("collectors"));  //暂存操作员
                        paymentJournal.setAccountName((String)map.get("pat_name")); //暂存患者姓名
                        paymentJournal.setCreatedTime(map.get("created_time") != null ? String.valueOf(map.get("created_time")) : null);
                        journals.add(paymentJournal);
                    }


                    XSSFFont titleFont = wb.createFont();
                    titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                    titleFont.setFontName("宋体");
                    titleFont.setFontHeightInPoints((short) 20);
                    XSSFCellStyle titleStyle = wb.createCellStyle();
                    titleStyle.setFont(titleFont);
                    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

                    // 生成一个样式
                    XSSFCellStyle style = titleStyle;
                    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//左右居中
                    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//上下居中


                    Collections.sort(journals, new Comparator<PaymentJournal>() {
                        @Override
                        public int compare(PaymentJournal o1, PaymentJournal o2) {
                            try {
                                Date dete1 = sdf.parse(o1.getCreatedTime() + " 00:00:00");
                                Date dete2 = sdf.parse(o2.getCreatedTime() + " 00:00:00");

                                if (dete1.getTime() > dete2.getTime()) {
                                    return 1;
                                }

                            } catch (ParseException e) {
                                e.printStackTrace();
                            }

                            return -1;
                        }
                    });

                    XSSFSheet sheet = wb.createSheet(datesName + " 平台订单流水明细");
                    // 产生表格标题行
                    int rowCount = 0;
                    XSSFRow ro = sheet.createRow(rowCount);
                    XSSFCell cel = ro.createCell(8);
                    cel.setCellValue(request.getOrgName() + "平台订单流水明细");
                    cel.setCellStyle(titleStyle);

                    sheet.setDefaultColumnWidth((short) 18);
                    XSSFRow row = sheet.createRow(rowCount + 1);
                    rowCount++;


                    Font ztFont = wb.createFont();
                    XSSFCellStyle ztStyle =  wb.createCellStyle();
                    ztFont.setFontHeightInPoints((short)12);
                    ztFont.setFontName("宋体");
                    ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    ztStyle.setFont(ztFont);
                    for (short i = 0; i < headers.length; i++) {
                        XSSFCell cell = row.createCell(i);
                        cell.setCellStyle(ztStyle);
                        XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }

                    int totalAmount = 0;
                    for (int i = 0; i < journals.size(); i++) {
                        int column = 0;
                        totalAmount += journals.get(i).getPayAmount();
                        XSSFRow row2 = sheet.createRow(rowCount + 1);
                        XSSFCell cell = row2.createCell(i);
                        cell.setCellStyle(style);
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(journals.get(i).getPayTime()) ? journals.get(i).getPayTime() : DateUtils.getDateStr(DateUtils.StringToDateYMD(journals.get(i).getPayTime())));
                        row2.createCell(column++).setCellValue(journals.get(i).getOrderNo());
                        row2.createCell(column++).setCellValue(journals.get(i).getHisOrderNo());
                        row2.createCell(column++).setCellValue(journals.get(i).getPaymentDealId());
                        row2.createCell(column++).setCellValue(NumberUtil.changeF2Y(String.valueOf(journals.get(i).getPayAmount())));
                        row2.createCell(column++).setCellValue(converOrderType(journals.get(i).getOrderType()));
                        row2.createCell(column++).setCellValue(converPayStatus(journals.get(i).getPayStatus()));
                        row2.createCell(column++).setCellValue(converCheckStatus(journals.get(i).getCheckStatus()));
                        row2.createCell(column++).setCellValue(converPayMode(journals.get(i).getPayMode()));
                        row2.createCell(column++).setCellValue(journals.get(i).getBranchName());
                        row2.createCell(column++).setCellValue(journals.get(i).getIsAutoRefund() == 0 ? "不是" : "是");
                        row2.createCell(column++).setCellValue(journals.get(i).getAccountName());
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(journals.get(i).getPayTime()) ? journals.get(i).getPayTime() : DateUtils.dateToString(DateUtils.StringToDate(journals.get(i).getPayTime())));
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(journals.get(i).getCreatedTime()) ? journals.get(i).getPayTime() :DateUtils.dateToString(DateUtils.StringToDate(journals.get(i).getCreatedTime())));
                        rowCount++;
                    }

                    XSSFRow row3 = sheet.createRow(rowCount + 1);
                    XSSFCell cell = row3.createCell(0);
                    cell.setCellStyle(style);
                    row3.createCell(0).setCellValue("合计金额：");
                    row3.createCell(4).setCellValue(NumberUtil.changeF2Y(String.valueOf(totalAmount)));


                    String savePath = yaml.getProperty(Config.CONFIG_PAY_ALIPAY_CHECK_ACCOUNT_PATH);
                    String fileName = request.getOrgName().trim() + "_平台订单流水明细.csv";
                    String filePath = URLDecoder.decode(savePath + fileName, "UTF-8");
                    out = new FileOutputStream(filePath);
                    wb.write(out);

                    logger.info("save success! " + filePath);
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("fileName", fileName);
                    map.put("filePath", filePath);

                    response.setResultCodeAndMessageAndResult(Response.SUCCESS_CODE, Response.SUCCESS_MESSAGE, JsonUtils.toJsonString(map));
                } catch (IOException e) {
                    logger.error("[FinanceExcelImpl.detailsPatstatisticsExcel] -- excelError", e);
                } finally {
                    if (out != null) {
                        try {
                            out.close();
                            wb.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } else {
                logger.info(request.getOrgId() + "_账单不存在.");
            }
        }

        return response;
    }


    /**
     * 收单机构订单流水明细Excel模板
     * @param datas
     * @param request
     * @return
     */
    public Response detailsThirdpaymentExcelTemplate(String datas, FinanceExcelVo request) {
        Response response = new Response();
        Map<String, Object> objMap = financeService.queryFinanceDetailsThirdpayment(datas);
        if (!objMap.isEmpty()) {
            NumberFormat nf = new DecimalFormat("#,###.####");
            List<PaymentCheckSourceFile> records = (List<PaymentCheckSourceFile>) objMap.get("records");
            String[] headers =
                    {"交易时间","平台订单号","收单机构流水号","金额","支付状态","支付类型","支付app_id","支付时间","创建时间"};
            XSSFWorkbook wb = new XSSFWorkbook();
            OutputStream out = null;
            String datesName = request.getBeginPayDate() + "—" + request.getEndPayDate();

            if (records.size() > 0) {
                try {

                    XSSFFont titleFont = wb.createFont();
                    titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                    titleFont.setFontName("宋体");
                    titleFont.setFontHeightInPoints((short) 20);
                    XSSFCellStyle titleStyle = wb.createCellStyle();
                    titleStyle.setFont(titleFont);
                    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

                    // 生成一个样式
                    XSSFCellStyle style = titleStyle;
                    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//左右居中
                    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//上下居中


                    Collections.sort(records, new Comparator<PaymentCheckSourceFile>() {
                        @Override
                        public int compare(PaymentCheckSourceFile o1, PaymentCheckSourceFile o2) {
                            try {
                                Date dete1 = sdf.parse(o1.getCreatedTime() + " 00:00:00");
                                Date dete2 = sdf.parse(o2.getCreatedTime() + " 00:00:00");

                                if (dete1.getTime() > dete2.getTime()) {
                                    return 1;
                                }

                            } catch (ParseException e) {
                                e.printStackTrace();
                            }

                            return -1;
                        }
                    });

                    XSSFSheet sheet = wb.createSheet(datesName + " 收单机构订单流水明细");
                    // 产生表格标题行
                    int rowCount = 0;
                    XSSFRow ro = sheet.createRow(rowCount);
                    XSSFCell cel = ro.createCell(8);
                    cel.setCellValue(request.getOrgName() + "收单机构订单流水明细");
                    cel.setCellStyle(titleStyle);

                    sheet.setDefaultColumnWidth((short) 18);
                    XSSFRow row = sheet.createRow(rowCount + 1);
                    rowCount++;


                    Font ztFont = wb.createFont();
                    XSSFCellStyle ztStyle =  wb.createCellStyle();
                    ztFont.setFontHeightInPoints((short)12);
                    ztFont.setFontName("宋体");
                    ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    ztStyle.setFont(ztFont);
                    for (short i = 0; i < headers.length; i++) {
                        XSSFCell cell = row.createCell(i);
                        cell.setCellStyle(ztStyle);
                        XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }

                    int totalAmount = 0;
                    for (int i = 0; i < records.size(); i++) {
                        int column = 0;
                        totalAmount += records.get(i).getPayAmount();
                        XSSFRow row2 = sheet.createRow(rowCount + 1);
                        XSSFCell cell = row2.createCell(i);
                        cell.setCellStyle(style);
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getPayTime()) ? records.get(i).getPayTime() : DateUtils.getDateStr(DateUtils.StringToDateYMD(records.get(i).getPayTime())));
                        row2.createCell(column++).setCellValue(records.get(i).getOrderNo());
                        row2.createCell(column++).setCellValue(records.get(i).getPaymentDealId());
                        row2.createCell(column++).setCellValue(NumberUtil.changeF2Y(String.valueOf(records.get(i).getPayAmount())));
                        row2.createCell(column++).setCellValue(converPayStatus(records.get(i).getPayStatus()));
                        row2.createCell(column++).setCellValue(converPayMode(records.get(i).getPayMode()));
                        row2.createCell(column++).setCellValue(records.get(i).getPayAppId());
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getPayTime()) ? records.get(i).getPayTime() : DateUtils.dateToString(DateUtils.StringToDate(records.get(i).getPayTime())));
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getCreatedTime()) ? records.get(i).getPayTime() :DateUtils.dateToString(DateUtils.StringToDate(records.get(i).getCreatedTime())));
                        rowCount++;
                    }

                    XSSFRow row3 = sheet.createRow(rowCount + 1);
                    XSSFCell cell = row3.createCell(0);
                    cell.setCellStyle(style);
                    row3.createCell(0).setCellValue("合计金额：");
                    row3.createCell(3).setCellValue(NumberUtil.changeF2Y(String.valueOf(totalAmount)));

                    String savePath = yaml.getProperty(Config.CONFIG_PAY_ALIPAY_CHECK_ACCOUNT_PATH);
                    String fileName = request.getOrgName().trim() + "_收单机构订单流水明细.csv";
                    String filePath = URLDecoder.decode(savePath + fileName, "UTF-8");
                    out = new FileOutputStream(filePath);
                    wb.write(out);

                    logger.info("save success! " + filePath);
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("fileName", fileName);
                    map.put("filePath", filePath);

                    response.setResultCodeAndMessageAndResult(Response.SUCCESS_CODE, Response.SUCCESS_MESSAGE, JsonUtils.toJsonString(map));
                } catch (IOException e) {
                    logger.error("[FinanceExcelImpl.detailsPatstatisticsExcel] -- excelError", e);
                } finally {
                    if (out != null) {
                        try {
                            out.close();
                            wb.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } else {
                logger.info(request.getOrgId() + "_账单不存在.");
            }
        }

        return response;
    }


    /**
     * 对账差错Excel模板
     * @param datas
     * @param request
     * @return
     */
    public Response financesDifferenceExcelTemplate(String datas, FinanceExcelVo request) {
        Response response = new Response();
        Map<String, Object> objMap = financeService.queryFinanceSummaryDifference(datas);
        if (!objMap.isEmpty()) {
            NumberFormat nf = new DecimalFormat("#,###.####");
            List<HisOrderCheckedError> records = (List<HisOrderCheckedError>) objMap.get("records");
            String[] headers =
                    {"平台订单号","HIS订单号","对账类型","交易渠道","支付状态","业务类型","医院应收金额","资金账户实收金额","服务商名称","操作员"};
            XSSFWorkbook wb = new XSSFWorkbook();
            OutputStream out = null;
            String datesName = request.getBeginPayDate() + "—" + request.getEndPayDate();

            if (records.size() > 0) {
                try {

                    XSSFFont titleFont = wb.createFont();
                    titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                    titleFont.setFontName("宋体");
                    titleFont.setFontHeightInPoints((short) 20);
                    XSSFCellStyle titleStyle = wb.createCellStyle();
                    titleStyle.setFont(titleFont);
                    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

                    // 生成一个样式
                    XSSFCellStyle style = titleStyle;
                    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//左右居中
                    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//上下居中

                    Collections.sort(records, new Comparator<HisOrderCheckedError>() {
                        @Override
                        public int compare(HisOrderCheckedError o1, HisOrderCheckedError o2) {
                            try {
                                Date dete1 = sdf.parse(o1.getCreatedTime() + " 00:00:00");
                                Date dete2 = sdf.parse(o2.getCreatedTime() + " 00:00:00");

                                if (dete1.getTime() > dete2.getTime()) {
                                    return 1;
                                }

                            } catch (ParseException e) {
                                e.printStackTrace();
                            }

                            return -1;
                        }
                    });

                    XSSFSheet sheet = wb.createSheet(datesName + " 对账差错明细");
                    // 产生表格标题行
                    int rowCount = 0;
                    XSSFRow ro = sheet.createRow(rowCount);
                    XSSFCell cel = ro.createCell(8);
                    cel.setCellValue(request.getOrgName() + "对账差错明细");
                    cel.setCellStyle(titleStyle);

                    sheet.setDefaultColumnWidth((short) 18);
                    XSSFRow row = sheet.createRow(rowCount + 1);
                    rowCount++;


                    Font ztFont = wb.createFont();
                    XSSFCellStyle ztStyle =  wb.createCellStyle();
                    ztFont.setFontHeightInPoints((short)12);
                    ztFont.setFontName("宋体");
                    ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    ztStyle.setFont(ztFont);
                    for (short i = 0; i < headers.length; i++) {
                        XSSFCell cell = row.createCell(i);
                        cell.setCellStyle(ztStyle);
                        XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }


                    for (int i = 0; i < records.size(); i++) {
                        int column = 0;
                        XSSFRow row2 = sheet.createRow(rowCount + 1);
                        XSSFCell cell = row2.createCell(i);
                        cell.setCellStyle(style);
                        row2.createCell(column++).setCellValue(records.get(i).getYytOrderNo());
                        row2.createCell(column++).setCellValue(records.get(i).getHisOrderNo());
                        row2.createCell(column++).setCellValue(converCheckStatus(records.get(i).getCheckStatus()));
                        row2.createCell(column++).setCellValue(converPayMode(records.get(i).getPayMode()));
                        row2.createCell(column++).setCellValue(converPayStatus(records.get(i).getPayStatus()));
                        row2.createCell(column++).setCellValue(converOrderType(records.get(i).getOrderType()));
                        row2.createCell(column++).setCellValue(records.get(i).getHisPayAmount() != null ? records.get(i).getHisPayAmount() : 0);
                        row2.createCell(column++).setCellValue(records.get(i).getYytPayAmount() != null ? records.get(i).getYytPayAmount() : 0);
                        row2.createCell(column++).setCellValue(records.get(i).getPlatformName());
                        row2.createCell(column++).setCellValue(records.get(i).getCollectors());
                        rowCount++;
                    }

                    String savePath = yaml.getProperty(Config.CONFIG_PAY_ALIPAY_CHECK_ACCOUNT_PATH);
                    String fileName = request.getOrgName().trim() + "_对账差错明细.csv";
                    String filePath = URLDecoder.decode(savePath + fileName, "UTF-8");
                    out = new FileOutputStream(filePath);
                    wb.write(out);

                    logger.info("save success! " + filePath);
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("fileName", fileName);
                    map.put("filePath", filePath);

                    response.setResultCodeAndMessageAndResult(Response.SUCCESS_CODE, Response.SUCCESS_MESSAGE, JsonUtils.toJsonString(map));
                } catch (IOException e) {
                    logger.error("[FinanceExcelImpl.detailsPatstatisticsExcel] -- excelError", e);
                } finally {
                    if (out != null) {
                        try {
                            out.close();
                            wb.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } else {
                logger.info(request.getOrgId() + "_账单不存在.");
            }
        }

        return response;
    }



    /**
     * 对账详情Excel模板
     * @param datas
     * @param request
     * @return
     */
    public Response financesRecordsExcelTemplate(String datas, FinanceExcelVo request) {
        Response response = new Response();
        Map<String, Object> objMap = financeService.queryFinanceRecords(datas);
        if (!objMap.isEmpty()) {
            NumberFormat nf = new DecimalFormat("#,###.####");
            List<FinanceRecordsVo> records = (List<FinanceRecordsVo>) objMap.get("records");
            String[] headers =
                    {"交易时间","对账类型","业务类型","支付状态","操作员","服务商名称","平台订单号","HIS订单号","医院应收金额","资金账户实收金额"};
            XSSFWorkbook wb = new XSSFWorkbook();
            OutputStream out = null;
            String datesName = request.getBeginPayDate() + "—" + request.getEndPayDate();

            if (records.size() > 0) {
                try {

                    XSSFFont titleFont = wb.createFont();
                    titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                    titleFont.setFontName("宋体");
                    titleFont.setFontHeightInPoints((short) 20);
                    XSSFCellStyle titleStyle = wb.createCellStyle();
                    titleStyle.setFont(titleFont);
                    titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

                    // 生成一个样式
                    XSSFCellStyle style = titleStyle;
                    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//左右居中
                    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//上下居中

                    Collections.sort(records, new Comparator<FinanceRecordsVo>() {
                        @Override
                        public int compare(FinanceRecordsVo o1, FinanceRecordsVo o2) {
                            try {
                                Date dete1 = sdf.parse(o1.getPay_time() + " 00:00:00");
                                Date dete2 = sdf.parse(o2.getPay_time() + " 00:00:00");

                                if (dete1.getTime() > dete2.getTime()) {
                                    return 1;
                                }

                            } catch (ParseException e) {
                                e.printStackTrace();
                            }

                            return -1;
                        }
                    });

                    XSSFSheet sheet = wb.createSheet(datesName + " 对账详情");
                    // 产生表格标题行
                    int rowCount = 0;
                    XSSFRow ro = sheet.createRow(rowCount);
                    XSSFCell cel = ro.createCell(8);
                    cel.setCellValue(request.getOrgName() + "对账详情");
                    cel.setCellStyle(titleStyle);

                    sheet.setDefaultColumnWidth((short) 18);
                    XSSFRow row = sheet.createRow(rowCount + 1);
                    rowCount++;


                    Font ztFont = wb.createFont();
                    XSSFCellStyle ztStyle =  wb.createCellStyle();
                    ztFont.setFontHeightInPoints((short)12);
                    ztFont.setFontName("宋体");
                    ztFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    ztStyle.setFont(ztFont);
                    for (short i = 0; i < headers.length; i++) {
                        XSSFCell cell = row.createCell(i);
                        cell.setCellStyle(ztStyle);
                        XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                        cell.setCellValue(text);
                    }


                    for (int i = 0; i < records.size(); i++) {
                        int column = 0;
                        XSSFRow row2 = sheet.createRow(rowCount + 1);
                        XSSFCell cell = row2.createCell(i);
                        cell.setCellStyle(style);
                        row2.createCell(column++).setCellValue(StringUtils.isBlank(records.get(i).getPay_time()) ? records.get(i).getPay_time() : DateUtils.dateToString(DateUtils.StringToDate(records.get(i).getPay_time())));
                        row2.createCell(column++).setCellValue(converCheckStatus(Short.valueOf(records.get(i).getCheck_status())));
                        row2.createCell(column++).setCellValue(converOrderType(Short.valueOf(records.get(i).getOrder_type())));
                        row2.createCell(column++).setCellValue(converPayStatus(Short.valueOf(records.get(i).getPay_status())));
                        row2.createCell(column++).setCellValue(records.get(i).getCollectors());
                        row2.createCell(column++).setCellValue(records.get(i).getPlatform_name());
                        row2.createCell(column++).setCellValue(records.get(i).getYyt_order_no());
                        row2.createCell(column++).setCellValue(records.get(i).getHis_order_no());
                        row2.createCell(column++).setCellValue(records.get(i).getHis_pay_amount());
                        row2.createCell(column++).setCellValue(records.get(i).getYyt_pay_amount());
                        rowCount++;
                    }

                    String savePath = yaml.getProperty(Config.CONFIG_PAY_ALIPAY_CHECK_ACCOUNT_PATH);
                    String fileName = request.getOrgName().trim() + "_对账详情.csv";
                    String filePath = URLDecoder.decode(savePath + fileName, "UTF-8");
                    out = new FileOutputStream(filePath);
                    wb.write(out);

                    logger.info("save success! " + filePath);
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("fileName", fileName);
                    map.put("filePath", filePath);

                    response.setResultCodeAndMessageAndResult(Response.SUCCESS_CODE, Response.SUCCESS_MESSAGE, JsonUtils.toJsonString(map));
                } catch (IOException e) {
                    logger.error("[FinanceExcelImpl.detailsPatstatisticsExcel] -- excelError", e);
                } finally {
                    if (out != null) {
                        try {
                            out.close();
                            wb.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } else {
                logger.info(request.getOrgId() + "_账单不存在.");
            }
        }

        return response;
    }


    /**
     * 交易状态转换
     * @return
     */
    public String converPayStatus(Short payStatus){
        String resultStr = "";
        switch (payStatus){
            case 1 : resultStr = "正常";
                break;
            case 2 : resultStr = "退款";
                break;
            case 3 : resultStr = "冲正";
                break;
            case 4 : resultStr = "对账插入（正常）";
                break;
            case 5 : resultStr = "对账插入（退款）";
                break;
            case 6 : resultStr = "出错";
                break;
            default: resultStr = payStatus.toString();
                break;
        }

        return resultStr;
    }

    /**
     * 订单类型转换
     * @return
     */
    public String converOrderType(Short orderType){
        String resultStr = "";
        switch (orderType){
            case 1 : resultStr = "挂号";
                break;
            case 2 : resultStr = "取号";
                break;
            case 3 : resultStr = "门诊缴费";
                break;
            case 4 : resultStr = "住院缴费";
                break;
            case 5 : resultStr = "预约平台";
                break;
            case 6 : resultStr = "病案复印";
                break;
            default: resultStr = orderType.toString();
                break;
        }

        return resultStr;
    }

    /**
     * 对账状态转换
     * @return
     */
    public String converCheckStatus(Short checkStatus){
        String resultStr = "";
        switch (checkStatus){
            case 0 : resultStr = "未对";
                break;
            case 1 : resultStr = "当日对平";
                break;
            case 2 : resultStr = "长款差错";
                break;
            case 3 : resultStr = "短款差错";
                break;
            case 4 : resultStr = "对账异常";
                break;
            default: resultStr = checkStatus.toString();
                break;
        }

        return resultStr;
    }


    /**
     * 支付渠道转换
     * @return
     */
    public String converPayMode(Short payMode){
        String resultStr = "";
        if("1".equals(payMode.toString().substring(0,1))){
            resultStr = "微信";
        }else if("2".equals(payMode.toString().substring(0,1))){
            resultStr = "支付宝";
        }else if("3".equals(payMode.toString().substring(0,1))){
            resultStr = "银联";
        }else{
            resultStr = payMode.toString();
        }

        return resultStr;
    }
}
